package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.dto.AdMaterialDataDTO;
import com.dy.yunying.api.vo.MaterialDataVO;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;

/**
 * 素材管理-数据报表
 * @author chenxiang
 * @className AdMaterialDataDao
 * @date 2023-2-27 15:06
 */
@Slf4j
@Component
@RequiredArgsConstructor
public class AdMaterialDataDao {

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	private final YunYingProperties prop;

	/**
	 * 查询数据报表
	 * @param material
	 * @return
	 */
	public List<AdMaterialDataDTO> materialData(MaterialDataVO material) {
		StringBuffer sqlBuilder = new StringBuffer();
		if ("1".equals(material.getExperience())){
			this.experienceDataSql(sqlBuilder,material);
		} else {
			this.deviceDataSql(sqlBuilder, material);
		}

		if (1 == material.getPeriod()){
			sqlBuilder.append(" ORDER BY period ASC ").append(" \n");
		}
		final String sql = sqlBuilder.toString();
		log.debug("素材管理-数据报表[{}]SQL: [\n{}]", this.getPeriodKey(material), sql);
		long start = System.currentTimeMillis();
		final List<AdMaterialDataDTO> list = clickhouseTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(AdMaterialDataDTO.class));
		long end = System.currentTimeMillis();
		log.info("素材管理-数据报表耗时: {}ms", end - start);
		return list;
	}

	public void deviceDataSql(StringBuffer sql, MaterialDataVO material) {
		sql.append(" SELECT ").append(" \n");
		sql.append("     period, ").append(" \n");
		sql.append("     COALESCE(showNum, 0) AS showNum, -- 展示数 ").append(" \n");
		sql.append("     COALESCE(clickNum, 0) AS clickNum, -- 点击数 ").append(" \n");
		sql.append("     toDecimal64(COALESCE(rudeCost, 0), 2) AS rudeCost, -- 原始消耗 ").append(" \n");
		sql.append("     toDecimal64(COALESCE(cost, 0), 2) AS cost, -- 返点后消耗 ").append(" \n");
		sql.append("     COALESCE(convertNum, 0) AS convertNum, -- 转化数 ").append(" \n");
		sql.append("     COALESCE(play100perNum, 0) AS playOverNum, -- 完播数 ").append(" \n");
		sql.append("     COALESCE(playValidNum, 0) AS playValidNum, -- 有效播放数 ").append(" \n");
		sql.append("     COALESCE(playTotalNum, 0) AS playTotalNum, -- 总播放数 ").append(" \n");
		sql.append("     toDecimal64(IF(showNum > 0, round(clickNum * 100 / IF(showNum > 0, showNum, 1), 2), 0), 2) AS showClickRate, -- 展示点击率(%) ").append(" \n");
		sql.append("     toDecimal64(IF(showNum > 0, round(playValidNum * 100 / IF(showNum > 0, showNum, 1), 2), 0), 2) AS playValidRate, -- 有效播放率(%) ").append(" \n");
		sql.append("     toDecimal64(IF(playTotalNum > 0, round(playOverNum * 100 / IF(playTotalNum > 0, playTotalNum, 1), 2), 0), 2) AS playOverRate, -- 完播率(%) ").append(" \n");
		sql.append("     toDecimal64(IF(clickNum > 0, round(convertNum * 100 / IF(clickNum > 0, clickNum, 1), 2), 0), 2) AS convertRate,  -- 转化率(%) ").append(" \n");
		sql.append("     toDecimal64(IF(convertNum > 0, round(cost / IF(convertNum > 0, convertNum, 1), 2), 0), 2) AS convertCost,  -- 转化成本 ").append(" \n");
		sql.append("     COALESCE(activeNum, 0) AS activeNum, -- 设备激活数 ").append(" \n");
		sql.append("     COALESCE(registerNum, 0) AS registerNum, -- 设备注册数 ").append(" \n");
		sql.append("     toDecimal64(IF(registerNum > 0, round(cost / IF(registerNum > 0, registerNum, 1), 2), 0), 2) AS registerCost, -- 注册成本 ").append(" \n");
		sql.append("     COALESCE(newPayNum, 0) AS newPayNum, -- 新增付费设备数 ").append(" \n");
		sql.append("     toDecimal64(IF(newPayNum > 0, round(cost / IF(newPayNum > 0, newPayNum, 1), 2), 0), 2) AS newPayCost, -- 新增付费成本 ").append(" \n");
		sql.append("     toDecimal64(COALESCE(totalPayFee2, 0), 2) AS totalPayFee, -- 累计付费实付金额 ").append(" \n");
		sql.append("     toDecimal64(IF(cost > 0, round(totalPayFee * 100 / IF(cost > 0, cost, 1), 2), 0), 2) AS totalRoi, -- 累计ROI(%) ").append(" \n");
		sql.append("     toDecimal64(IF(registerNum > 0, round(retention2Num * 100 / IF(registerNum > 0, registerNum, 1), 2), 0), 2) AS retention2, -- 次留 ").append(" \n");
		sql.append("     toDecimal64(IF(registerNum > 0, round(retention7Num * 100 / IF(registerNum > 0, registerNum, 1), 2), 0), 2) AS retention7, -- 7留 ").append(" \n");
		sql.append("     toDecimal64(IF(registerNum > 0, round(retention30Num * 100 / IF(registerNum > 0, registerNum, 1), 2), 0), 2) AS retention30 -- 30留 ").append(" \n");
		sql.append(" FROM ").append(" \n");
		sql.append("     ( ").append(" \n");
		sql.append("         SELECT ").append(" \n");
		sql.append("             ").append(this.getPeriodKey(material)).append(" AS period,  ").append(" \n");
		sql.append("             SUM(show_num) AS showNum, -- 展示数 ").append(" \n");
		sql.append("             SUM(click_num) AS clickNum, -- 点击数 ").append(" \n");
		sql.append("             SUM(rude_cost) AS rudeCost, -- 原始消耗 ").append(" \n");
		sql.append("             SUM(cost) AS cost, -- 返点后消耗 ").append(" \n");
		sql.append("             SUM(convert) AS convertNum, -- 转化数 ").append(" \n");
		sql.append("             SUM(play_100per_num) AS play100perNum, -- 100%播放数 ").append(" \n");
		sql.append("             SUM(play_valid_num) AS playValidNum, -- 有效播放数 ").append(" \n");
		sql.append("             SUM(play_total_num) AS playTotalNum -- 总播放数 ").append(" \n");
		sql.append("         FROM ").append(" \n");
		sql.append("             ( ").append(" \n");
		sql.append("                 SELECT ").append(" \n");
		sql.append("                     day, week, month, year, collect, ctype, parentchl, os, creative_id AS creativeId, show_num, click_num, rude_cost, cost, ").append(" \n");
		sql.append("                     play_100per_num, play_valid_num, play_total_num, convert ").append(" \n");
		sql.append("                 FROM ").append(" \n");
		sql.append("                     ").append(prop.getCreativeDayReport()).append(" \n");
		sql.append("                 WHERE ").append(" \n");
		sql.append("                     show_num > 0 AND day >= ").append(material.getSdate()).append(" AND day <= ").append(material.getEdate()).append(" \n");
		sql.append("                     ").append(this.getChlWhereSql(material)).append(" \n");
		sql.append("             ) cst ").append(" \n");
		sql.append("             LEFT JOIN (SELECT platform_id, creative_id,any(material_id) as material_id, any(landing_page_id) AS landingPageId FROM (SELECT id,creative_id,material_id,landing_page_id,platform_id FROM dim_200_pangu_mysql_ad_creative_material ORDER BY id desc)  GROUP BY creative_id , platform_id) cm ON cst.ctype = toInt8(cm.platform_id) AND cst.creativeId = toString(cm.creative_id) ").append(" \n");
		sql.append("             LEFT JOIN ( ").append(" \n");
		sql.append("                 SELECT ").append(" \n");
		sql.append("                     CAST(mat.id AS Nullable(UInt64)) AS materialId, mat.name AS materialName, mat.type AS materialType, mat.creator_id AS creatorId, mat.maker_id AS makerId, mat.main_game_id AS pgid, mat.make_type AS makeType, ").append(" \n");
		sql.append("                     mat.width AS materialWidth, mat.height AS materialHeight, CAST(mat.selling_point_id AS Nullable(Int64)) AS sellingPointId, dsp.name AS sellingPointName ").append(" \n");
		sql.append("                 FROM ").append(" \n");
		sql.append("                     dim_200_pangu_mysql_ad_material mat ").append(" \n");
		sql.append("                     LEFT JOIN v_odsmysql_design_selling_point dsp ON mat.selling_point_id = toInt64(dsp.id) ").append(" \n");
		sql.append("                 WHERE ").append(" \n");
		sql.append("                     screen_type != 7 ").append(" \n");
		sql.append("             ) mat ON cm.material_id = mat.materialId ").append(" \n");
		sql.append("         WHERE ").append(" \n");
		sql.append("             1 = 1 ").append(this.getMaterialWhereSql(material)).append(" \n");
		sql.append("         GROUP BY period ").append(" \n");
		sql.append("     ) cst ").append(" \n");
		sql.append("     FULL JOIN ( ").append(" \n");
		sql.append("         SELECT ").append(" \n");
		sql.append("             ").append(this.getPeriodKey(material)).append(" AS period, ").append(" \n");
		sql.append("             COUNT(DISTINCT kid) activeNum, -- 设备激活数 ").append(" \n");
		sql.append("             SUM(IF(LENGTH(latest_username) > 0, 1, 0)) AS registerNum, -- 新增设备注册数 ").append(" \n");
		sql.append("             SUM(fee_1) AS newPayFee1, -- 新增付费实付金额（分成前） ").append(" \n");
		sql.append("             SUM(fee_1 * sharing) AS newPayFee2, -- 新增付费实付金额（分成后） ").append(" \n");
		sql.append("             SUM(givemoney_1) AS newPayGivemoney1, -- 新增付费代金券金额（分成前） ").append(" \n");
		sql.append("             SUM(givemoney_1 * sharing) AS newPayGivemoney2, -- 新增付费代金券金额（分成后） ").append(" \n");
		sql.append("             SUM(IF(first_pay_day = day, 1, 0)) AS newPayNum, -- 新增付费设备数 ").append(" \n");
		sql.append("             SUM(IF(LENGTH(latest_username) > 0 AND day = first_pay_day, 1, 0)) newRegPayNum, -- 新增注册付费设备数 ").append(" \n");
		sql.append("             SUM(fee_total) AS totalPayFee1, -- 累计付费实付金额（分成前） ").append(" \n");
		sql.append("             SUM(fee_total * sharing) AS totalPayFee2, -- 累计付费实付金额（分成后） ").append(" \n");
		sql.append("             SUM(givemoney_total) AS totalPayGivemoney1, -- 累计付费代金券金额（分成前） ").append(" \n");
		sql.append("             SUM(givemoney_total * sharing) AS totalPayGivemoney2, -- 累计付费代金券金额（分成后） ").append(" \n");
		sql.append("             SUM(is_2_retention) AS retention2Num, -- 次留设备数 ").append(" \n");
		sql.append("             SUM(IF(LENGTH(latest_username) > 0 AND day = first_pay_day, is_2_retention, 0)) AS payRetention2Num, -- 付费次留设备数 ").append(" \n");
		sql.append("             SUM(is_7_retention) AS retention7Num, -- 7留设备数 ").append(" \n");
		sql.append("             SUM(is_30_retention) AS retention30Num -- 30留设备数 ").append(" \n");
		sql.append("         FROM ").append(" \n");
		sql.append("             ( ").append(" \n");
		sql.append("                 SELECT ").append(" \n");
		sql.append("                     reg_day day, week, month, year, collect, kid, game_main pgid1, os, game_sub gameid, chl_main parentchl, chl_sub chl, chl_base appchl, ctype, cid AS creativeId, latest_username, ").append(" \n");
		sql.append("                     first_pay_day, fee_1, givemoney_1, fee_total, givemoney_total, is_2_retention, is_7_retention, is_30_retention ").append(" \n");
		sql.append("                 FROM ").append(" \n");
		sql.append("                     ").append(prop.getNinetydeviceregtable()).append(" \n");
		sql.append("                 WHERE ").append(" \n");
		sql.append("                     spread_type = 1 AND day >= ").append(material.getSdate()).append(" AND day <= ").append(material.getEdate()).append(" \n");
		sql.append("                     ").append(this.getChlWhereSql(material)).append(" \n");
		sql.append("             ) reg ").append(" \n");
		sql.append("             LEFT JOIN (SELECT platform_id, creative_id,any(material_id) as material_id, any(landing_page_id) AS landingPageId FROM (SELECT id,creative_id,material_id,landing_page_id,platform_id FROM dim_200_pangu_mysql_ad_creative_material ORDER BY id desc)  GROUP BY creative_id , platform_id) cm ON reg.ctype = toInt8(cm.platform_id) AND reg.creativeId = toString(cm.creative_id) ").append(" \n");
		sql.append("             LEFT JOIN ( ").append(" \n");
		sql.append("                 SELECT ").append(" \n");
		sql.append("                     CAST(mat.id AS Nullable(UInt64)) AS materialId, mat.name AS materialName, mat.type AS materialType, mat.creator_id AS creatorId, mat.maker_id AS makerId, mat.main_game_id AS pgid, mat.make_type AS makeType, ").append(" \n");
		sql.append("                     mat.width AS materialWidth, mat.height AS materialHeight, CAST(mat.selling_point_id AS Nullable(Int64)) AS sellingPointId, dsp.name AS sellingPointName ").append(" \n");
		sql.append("                 FROM ").append(" \n");
		sql.append("                     dim_200_pangu_mysql_ad_material mat ").append(" \n");
		sql.append("                     LEFT JOIN v_odsmysql_design_selling_point dsp ON mat.selling_point_id = toInt64(dsp.id) ").append(" \n");
		sql.append("                 WHERE ").append(" \n");
		sql.append("                     screen_type != 7 ").append(" \n");
		sql.append("             ) mat ON cm.material_id = mat.materialId ").append(" \n");
		sql.append("             LEFT JOIN (SELECT CAST(id AS Int16) AS id, (1 - COALESCE(sharing, 0)) sharing FROM dim_200_pangu_mysql_parent_game pg_tmp) pg ON reg.pgid1 = pg.id ").append(" \n");
		sql.append("         WHERE ").append(" \n");
		sql.append("             1 = 1 ").append(this.getMaterialWhereSql(material)).append(" \n");
		sql.append("         GROUP BY period ").append(" \n");
		sql.append("     ) reg USING (period) ").append(" \n");
	}

	/**
	 * 体验版
	 * @param sql
	 * @param material
	 */
	private void experienceDataSql(StringBuffer sql, MaterialDataVO material) {
		sql.append(" SELECT ").append(" \n");
		sql.append("     period, ").append(" \n");
		sql.append("     COALESCE(showNum, 0) AS showNum, -- 展示数 ").append(" \n");
		sql.append("     COALESCE(clickNum, 0) AS clickNum, -- 点击数 ").append(" \n");
		sql.append("     toDecimal64(COALESCE(rudeCost, 0), 2) AS rudeCost, -- 原始消耗 ").append(" \n");
		sql.append("     toDecimal64(COALESCE(cost, 0), 2) AS cost, -- 返点后消耗 ").append(" \n");
		sql.append("     COALESCE(convertNum, 0) AS convertNum, -- 转化数 ").append(" \n");
		sql.append("     COALESCE(activeNum, 0) AS activeNum, -- 设备激活数 ").append(" \n");
		sql.append("     COALESCE(registerNum, 0) AS registerNum, -- 设备注册数 ").append(" \n");
		sql.append("     toDecimal64(COALESCE(newPayFee2, 0), 2) AS newPayFee, -- 新增付费实付金额 ").append(" \n");
		sql.append("     toDecimal64(COALESCE(newPayGivemoney2, 0), 2) AS newPayGivemoney, -- 新增付费代金券金额 ").append(" \n");
		sql.append("     COALESCE(newPayNum, 0) AS newPayNum, -- 新增付费设备数 ").append(" \n");
		sql.append("     toDecimal64(COALESCE(totalPayFee2, 0), 2) AS totalPayFee, -- 累计付费实付金额 ").append(" \n");
		sql.append("     toDecimal64(COALESCE(totalPayGivemoney2, 0), 2) AS totalPayGivemoney, -- 累计付费代金券金额 ").append(" \n");
		sql.append("     COALESCE(retention2Num, 0) AS retention2Num, -- 次留设备数 ").append(" \n");
		sql.append("     COALESCE(retention7Num, 0) AS retention7Num, -- 7留设备数 ").append(" \n");
		sql.append("     COALESCE(retention30Num, 0) AS retention30Num, -- 30留设备数 ").append(" \n");
		sql.append("     toDecimal64(IF(showNum > 0, round(clickNum * 100 / IF(showNum = 0, 1, showNum), 2), 0), 2) AS showClickRate, -- 展示点击率(%) ").append(" \n");
		sql.append("     toDecimal64(IF(clickNum > 0, round(activeNum * 100 / IF(clickNum = 0, 1, clickNum), 2), 0), 2) AS clickActiveRate, -- 点击激活率(%) ").append(" \n");
		sql.append("     toDecimal64(IF(clickNum > 0, round(registerNum * 100 / IF(clickNum = 0, 1, clickNum), 2), 0), 2) AS clickRegisterRate, -- 点击注册率(%) ").append(" \n");
		sql.append("     toDecimal64(IF(registerNum > 0, round(cost / IF(registerNum > 0, registerNum, 1), 2), 0), 2) AS registerCost, -- 注册成本 ").append(" \n");
		sql.append("     toDecimal64(IF(newRegPayNum > 0, round(payRetention2Num * 100 / IF(newRegPayNum > 0, newRegPayNum, 1), 2), 0), 2) AS newPayRetention2, -- 新增付费次留(%) ").append(" \n");
		sql.append("     toDecimal64(IF(registerNum > 0, round(newPayNum * 100 / IF(registerNum > 0, registerNum, 1), 2), 0), 2) AS newPayRate, -- 新增付费率(%) ").append(" \n");
		sql.append("     toDecimal64(IF(newPayNum > 0, round(cost / IF(newPayNum > 0, newPayNum, 1), 2), 0), 2) AS newPayCost, -- 新增付费成本 ").append(" \n");
		sql.append("     toDecimal64(IF(cost > 0, round(totalPayFee * 100 / IF(cost > 0, cost, 1), 2), 0), 2) AS totalRoi, -- 累计ROI(%) ").append(" \n");
		sql.append("     toDecimal64(IF(cost > 0, round(newPayFee * 100 / IF(cost > 0, cost, 1), 2), 0), 2) AS firstRoi, -- 首日ROI(%) ").append(" \n");
		sql.append("     toDecimal64(IF(registerNum > 0, round(retention2Num * 100 / IF(registerNum > 0, registerNum, 1), 2), 0), 2) AS retention2, -- 次留 ").append(" \n");
		sql.append("     toDecimal64(IF(registerNum > 0, round(retention7Num * 100 / IF(registerNum > 0, registerNum, 1), 2), 0), 2) AS retention7, -- 7留 ").append(" \n");
		sql.append("     toDecimal64(IF(registerNum > 0, round(retention30Num * 100 / IF(registerNum > 0, registerNum, 1), 2), 0), 2) AS retention30, -- 30留 ").append(" \n");
		sql.append("     toDecimal64(IF(clickNum > 0, round(convertNum * 100 / IF(clickNum > 0, clickNum, 1), 2), 0), 2) AS convertRate,  -- 转化率(%) ").append(" \n");
		sql.append("     toDecimal64(IF(convertNum > 0, round(cost / IF(convertNum > 0, convertNum, 1), 2), 0), 2) AS convertCost  -- 转化成本 ").append(" \n");
		sql.append(" FROM ").append(" \n");
		sql.append("     ( ").append(" \n");
		sql.append("         SELECT ").append(" \n");
		sql.append("             period, ").append(" \n");
		sql.append("             showNum, -- 展示数 ").append(" \n");
		sql.append("             clickNum, -- 点击数 ").append(" \n");
		sql.append("             rudeCost, -- 原始消耗 ").append(" \n");
		sql.append("             cost, -- 返点后消耗 ").append(" \n");
		sql.append("             activeNum, -- 设备激活数 ").append(" \n");
		sql.append("             registerNum, -- 设备注册数 ").append(" \n");
		sql.append("             newPayFee1, -- 新增付费实付金额（分成前） ").append(" \n");
		sql.append("             newPayFee2, -- 新增付费实付金额（分成后） ").append(" \n");
		sql.append("             newPayGivemoney1, -- 新增付费代金券金额（分成前） ").append(" \n");
		sql.append("             newPayGivemoney2, -- 新增付费代金券金额（分成后） ").append(" \n");
		sql.append("             newPayNum, -- 新增付费设备数 ").append(" \n");
		sql.append("             newRegPayNum, -- 新增注册付费设备数 ").append(" \n");
		sql.append("             totalPayFee1, -- 累计付费实付金额（分成前） ").append(" \n");
		sql.append("             totalPayFee2, -- 累计付费实付金额（分成后） ").append(" \n");
		sql.append("             totalPayGivemoney1, -- 累计付费代金券金额（分成前） ").append(" \n");
		sql.append("             totalPayGivemoney2, -- 累计付费代金券金额（分成后） ").append(" \n");
		sql.append("             retention2Num, -- 次留设备数 ").append(" \n");
		sql.append("             payRetention2Num, -- 付费次留设备数 ").append(" \n");
		sql.append("             retention7Num, -- 7留设备数 ").append(" \n");
		sql.append("             retention30Num, -- 30留设备数 ").append(" \n");
		sql.append("             convertNum ").append(" \n");
		sql.append("         FROM ").append(" \n");
		sql.append("             ( ").append(" \n");
		sql.append("                 SELECT ").append(" \n");
		sql.append("                     ").append(this.getPeriodKey(material)).append(" AS period, ").append(" \n");
		sql.append("                     SUM(show_num) AS showNum, -- 展示数 ").append(" \n");
		sql.append("                     SUM(click_num) AS clickNum, -- 点击数 ").append(" \n");
		sql.append("                     SUM(rude_cost) AS rudeCost, -- 原始消耗 ").append(" \n");
		sql.append("                     SUM(cost) AS cost, -- 返点后消耗 ").append(" \n");
		sql.append("                     SUM(convert) AS convertNum -- 转化数 ").append(" \n");
		sql.append("                 FROM ").append(" \n");
		sql.append("                     ( ").append(" \n");
		sql.append("                     		SELECT ").append(" \n");
		sql.append("                       	    day, week, month, year, collect, ctype, parentchl, os, material_id AS creativeId, promotion_id, show_num, click_num, rude_cost, cost, convert ").append(" \n");
		sql.append("                        	FROM ").append(" \n");
		sql.append("                             	").append(prop.getMaterialDayReport()).append(" \n");
		sql.append("                         	WHERE ").append(" \n");
		sql.append("                             	show_num > 0 AND image_mode in('CREATIVE_IMAGE_MODE_VIDEO','CREATIVE_IMAGE_MODE_VIDEO_VERTICA') AND day >= ").append(material.getSdate()).append(" AND day <= ").append(material.getEdate()).append(" \n");
		sql.append("                             	").append(this.getChlWhereSql(material)).append(" \n");
		sql.append("                     ) cst ").append(" \n");
		sql.append("                     LEFT JOIN (SELECT platform_id, ad_id, creative_id, material_id, landing_page_id AS landingPageId FROM dim_200_pangu_mysql_ad_creative_material where version_type = 2 and material_type = 1 ) cm ON cst.ctype = toInt8(cm.platform_id) AND toString(cst.creativeId) = toString(cm.creative_id) ").append(" \n");
		sql.append("                     LEFT JOIN ( ").append(" \n");
		sql.append("                         SELECT ").append(" \n");
		sql.append("                             CAST(mat.id AS Nullable(UInt64)) AS materialId, mat.name AS materialName, mat.type AS materialType, mat.creator_id AS creatorId, mat.maker_id AS makerId, mat.main_game_id AS pgid, mat.make_type AS makeType, ").append(" \n");
		sql.append("                             mat.width AS materialWidth, mat.height AS materialHeight, CAST(mat.selling_point_id AS Nullable(Int64)) AS sellingPointId, dsp.name AS sellingPointName ").append(" \n");
		sql.append("                         FROM ").append(" \n");
		sql.append("                             dim_200_pangu_mysql_ad_material mat ").append(" \n");
		sql.append("                             LEFT JOIN v_odsmysql_design_selling_point dsp ON mat.selling_point_id = toInt64(dsp.id) ").append(" \n");
		sql.append("                         WHERE ").append(" \n");
		sql.append("                             screen_type != 7 ").append(" \n");
		sql.append("                     ) mat ON cm.material_id = mat.materialId ").append(" \n");
		sql.append("                 WHERE ").append(" \n");
		sql.append("                     1 = 1 ").append(this.getMaterialWhereSql(material)).append(" \n");
		sql.append("                 GROUP BY ").append(" \n");
		sql.append("                     period ").append(" \n");
		sql.append("             ) cst ").append(" \n");
		sql.append("             FULL JOIN ( ").append(" \n");
		sql.append("                 SELECT ").append(" \n");
		sql.append("                     ").append(this.getPeriodKey(material)).append(" AS period, ").append(" \n");
		sql.append("                     COUNT(DISTINCT kid) activeNum, -- 设备激活数 ").append(" \n");
		sql.append("                     SUM(IF(LENGTH(latest_username) > 0, 1, 0)) AS registerNum, -- 新增设备注册数 ").append(" \n");
		sql.append("                     SUM(fee_1) AS newPayFee1, -- 新增付费实付金额（分成前） ").append(" \n");
		sql.append("                     SUM(fee_1 * sharing) AS newPayFee2, -- 新增付费实付金额（分成后） ").append(" \n");
		sql.append("                     SUM(givemoney_1) AS newPayGivemoney1, -- 新增付费代金券金额（分成前） ").append(" \n");
		sql.append("                     SUM(givemoney_1 * sharing) AS newPayGivemoney2, -- 新增付费代金券金额（分成后） ").append(" \n");
		sql.append("                     SUM(IF(first_pay_day = day, 1, 0)) AS newPayNum, -- 新增付费设备数 ").append(" \n");
		sql.append("                     SUM(IF(LENGTH(latest_username) > 0 AND day = first_pay_day, 1, 0)) newRegPayNum, -- 新增注册付费设备数 ").append(" \n");
		sql.append("                     SUM(fee_total) AS totalPayFee1, -- 累计付费实付金额（分成前） ").append(" \n");
		sql.append("                     SUM(fee_total * sharing) AS totalPayFee2, -- 累计付费实付金额（分成后） ").append(" \n");
		sql.append("                     SUM(givemoney_total) AS totalPayGivemoney1, -- 累计付费代金券金额（分成前） ").append(" \n");
		sql.append("                     SUM(givemoney_total * sharing) AS totalPayGivemoney2, -- 累计付费代金券金额（分成后） ").append(" \n");
		sql.append("                     SUM(is_2_retention) AS retention2Num, -- 次留设备数 ").append(" \n");
		sql.append("                     SUM(IF(LENGTH(latest_username) > 0 AND day = first_pay_day, is_2_retention, 0)) AS payRetention2Num, -- 付费次留设备数 ").append(" \n");
		sql.append("                     SUM(is_7_retention) AS retention7Num, -- 7留设备数 ").append(" \n");
		sql.append("                     SUM(is_30_retention) AS retention30Num -- 30留设备数 ").append(" \n");
		sql.append("                 FROM ").append(" \n");
		sql.append("                     ( ").append(" \n");
		sql.append("                         SELECT ").append(" \n");
		sql.append("                             reg_day day, week, month, year, collect, kid, game_main pgid1, os, game_sub gameid, chl_main parentchl, chl_sub chl, chl_base appchl, ctype, cid AS creativeId, mid3, mid5, latest_username, ").append(" \n");
		sql.append("                             first_pay_day, fee_1, givemoney_1, fee_total, givemoney_total, is_2_retention, is_7_retention, is_30_retention ").append(" \n");
		sql.append("                         FROM ").append(" \n");
		sql.append("                             ").append(prop.getNinetydeviceregtable()).append(" \n");
		sql.append("                         WHERE ").append(" \n");
		sql.append("                             spread_type = 1 and mid3 != '' and mid3 is not null AND day >= ").append(material.getSdate()).append(" AND day <= ").append(material.getEdate()).append(" \n");
		sql.append("                             ").append(this.getChlWhereSql(material)).append(" \n");
		sql.append("                     ) reg ").append(" \n");
		sql.append("                     LEFT JOIN (SELECT platform_id, version_type, material_type, creative_id, material_id, landing_page_id AS landingPageId FROM dim_200_pangu_mysql_ad_creative_material where version_type = 2 and material_type = 1 ) cm ON reg.ctype = toInt8(cm.platform_id) AND reg.mid3 = toString(cm.creative_id) ").append(" \n");
		sql.append("                     LEFT JOIN ( ").append(" \n");
		sql.append("                         SELECT ").append(" \n");
		sql.append("                             CAST(mat.id AS Nullable(UInt64)) AS materialId, mat.name AS materialName, mat.type AS materialType, mat.creator_id AS creatorId, mat.maker_id AS makerId, mat.main_game_id AS pgid, mat.make_type AS makeType, ").append(" \n");
		sql.append("                             mat.width AS materialWidth, mat.height AS materialHeight, CAST(mat.selling_point_id AS Nullable(Int64)) AS sellingPointId, dsp.name AS sellingPointName ").append(" \n");
		sql.append("                         FROM ").append(" \n");
		sql.append("                             dim_200_pangu_mysql_ad_material mat ").append(" \n");
		sql.append("                             LEFT JOIN v_odsmysql_design_selling_point dsp ON mat.selling_point_id = toInt64(dsp.id) ").append(" \n");
		sql.append("                         WHERE ").append(" \n");
		sql.append("                             screen_type != 7 ").append(" \n");
		sql.append("                     ) mat ON cm.material_id = mat.materialId ").append(" \n");
		sql.append("                     LEFT JOIN (SELECT CAST(id AS Int16) AS id, (1 - COALESCE(sharing, 0)) sharing FROM dim_200_pangu_mysql_parent_game pg_tmp) pg ON reg.pgid1 = pg.id ").append(" \n");
		sql.append("                 WHERE 1 = 1 ").append(this.getMaterialWhereSql(material)).append(" \n");
		sql.append("                 GROUP BY period ").append(" \n");
		sql.append("             ) reg USING (period) ").append(" \n");
		sql.append("     ) quota ").append(" \n");
	}


	private String getChlWhereSql(MaterialDataVO material) {
		final String parentchl = StringUtils.trim(material.getParentchl());
		final StringBuilder builder = new StringBuilder();
		if (StringUtils.isNotBlank(parentchl)) {
			builder.append(" AND parentchl IN ('").append(parentchl.replaceAll(",", "','")).append("')");
		}
		return builder.toString();
	}

	private String getMaterialWhereSql(MaterialDataVO material) {
		final StringBuilder builder = new StringBuilder();
		final Integer creatorId = material.getCreatorId();
		final Integer makerId = material.getMakerId();
		final String materialIds = material.getMaterialIds();

		if (null != creatorId) {
			builder.append(" AND creatorId = ").append(creatorId);
		}
		if (null != makerId) {
			builder.append(" AND makerId = ").append(makerId);
		}
		if (StringUtils.isNotBlank(materialIds)) {
			builder.append(" AND materialId IN (").append(materialIds).append(")");
		}
		return builder.toString();
	}

	private String getPeriodKey(MaterialDataVO material) {
		final Integer period = material.getPeriod();
		if (null == period || 4 == period) {
			return "collect";
		} else if (1 == period) {
			return "day";
		} else if (2 == period) {
			return "week";
		} else if (3 == period) {
			return "month";
		}
		return "collect";
	}

	/*	public void adDataSql(StringBuffer sql, MaterialDataVO material) {
		sql.append(" SELECT  ").append(" \n");
		sql.append(" 	period, ").append(" \n");
		sql.append(" 	COALESCE(showNum, 0) AS showNum, -- 展示数 ").append(" \n");
		sql.append("     COALESCE(clickNum, 0) AS clickNum, -- 点击数 ").append(" \n");
		sql.append("     toDecimal64(COALESCE(rudeCost, 0), 2) AS rudeCost, -- 原始消耗 ").append(" \n");
		sql.append("     toDecimal64(COALESCE(cost, 0), 2) AS cost, -- 返点后消耗 ").append(" \n");
		sql.append("     COALESCE(convertNum, 0) AS convertNum, -- 转化数 ").append(" \n");
		sql.append("     COALESCE(play100perNum, 0) AS playOverNum, -- 完播数 ").append(" \n");
		sql.append("     COALESCE(playValidNum, 0) AS playValidNum, -- 有效播放数 ").append(" \n");
		sql.append("     COALESCE(playTotalNum, 0) AS playTotalNum, -- 总播放数 ").append(" \n");
		sql.append("     toDecimal64(IF(showNum > 0, round(clickNum * 100 / IF(showNum > 0, showNum, 1), 2), 0), 2) AS showClickRate, -- 展示点击率(%) ").append(" \n");
		sql.append("     toDecimal64(IF(showNum > 0, round(playValidNum * 100 / IF(showNum > 0, showNum, 1), 2), 0), 2) AS playValidRate, -- 有效播放率(%) ").append(" \n");
		sql.append("     toDecimal64(IF(playTotalNum > 0, round(playOverNum * 100 / IF(playTotalNum > 0, playTotalNum, 1), 2), 0), 2) AS playOverRate, -- 完播率(%) ").append(" \n");
		sql.append("     toDecimal64(IF(clickNum > 0, round(convertNum * 100 / IF(clickNum > 0, clickNum, 1), 2), 0), 2) AS convertRate,  -- 转化率(%) ").append(" \n");
		sql.append("     toDecimal64(IF(convertNum > 0, round(cost / IF(convertNum > 0, convertNum, 1), 2), 0), 2) AS convertCost  -- 转化成本 ").append(" \n");
		sql.append(" FROM ( ").append(" \n");
		sql.append("     SELECT ").append(" \n");
		sql.append("         ").append(this.getPeriodKey(material)).append(" AS period, ").append(" \n");
		sql.append("         SUM(show_num) AS showNum, -- 展示数 ").append(" \n");
		sql.append("         SUM(click_num) AS clickNum, -- 点击数 ").append(" \n");
		sql.append("         SUM(rude_cost) AS rudeCost, -- 原始消耗 ").append(" \n");
		sql.append("         SUM(cost) AS cost, -- 返点后消耗 ").append(" \n");
		sql.append("         SUM(convert) AS convertNum, -- 转化数 ").append(" \n");
		sql.append("         SUM(play_100per_num) AS play100perNum, -- 100%播放数 ").append(" \n");
		sql.append("         SUM(play_valid_num) AS playValidNum, -- 有效播放数 ").append(" \n");
		sql.append("         SUM(play_total_num) AS playTotalNum -- 总播放数 ").append(" \n");
		sql.append("     FROM ").append(" \n");
		sql.append("         ( ").append(" \n");
		sql.append("             SELECT ").append(" \n");
		sql.append("                 day, week, month, year, collect, ctype, parentchl, os, creative_id AS creativeId,  ").append(" \n");
		sql.append("                 show_num, click_num, rude_cost, cost, convert,play_100per_num, play_valid_num, play_total_num ").append(" \n");
		sql.append("             FROM ").append(" \n");
		sql.append("                 ").append(prop.getCreativeDayReport()).append(" \n");
		sql.append("             WHERE ").append(" \n");
		sql.append("                 show_num > 0 AND day >= ").append(material.getSdate()).append(" AND day <= ").append(material.getEdate()).append(" \n");
		sql.append("                 ").append(this.getChlWhereSql(material)).append(" \n");
		sql.append("         ) cst ").append(" \n");
		sql.append("         LEFT JOIN (SELECT platform_id, creative_id,any(material_id) as material_id, any(landing_page_id) AS landingPageId FROM (SELECT id,creative_id,material_id,landing_page_id,platform_id FROM dim_200_pangu_mysql_ad_creative_material ORDER BY id desc)  GROUP BY creative_id , platform_id) cm ON cst.ctype = toInt8(cm.platform_id) AND cst.creativeId = toString(cm.creative_id) ").append(" \n");
		sql.append("         LEFT JOIN ( ").append(" \n");
		sql.append("             SELECT ").append(" \n");
		sql.append("                 CAST(mat.id AS Nullable(UInt64)) AS materialId, mat.name AS materialName, mat.type AS materialType, mat.creator_id AS creatorId, mat.maker_id AS makerId, mat.main_game_id AS pgid, mat.make_type AS makeType, ").append(" \n");
		sql.append("                 mat.width AS materialWidth, mat.height AS materialHeight, CAST(mat.selling_point_id AS Nullable(Int64)) AS sellingPointId, dsp.name AS sellingPointName ").append(" \n");
		sql.append("             FROM ").append(" \n");
		sql.append("                 dim_200_pangu_mysql_ad_material mat ").append(" \n");
		sql.append("                 LEFT JOIN v_odsmysql_design_selling_point dsp ON mat.selling_point_id = toInt64(dsp.id) ").append(" \n");
		sql.append("             WHERE ").append(" \n");
		sql.append("                 screen_type != 7 ").append(" \n");
		sql.append("         ) mat ON cm.material_id = mat.materialId ").append(" \n");
		sql.append("     WHERE ").append(" \n");
		sql.append("         1 = 1 ").append(this.getMaterialWhereSql(material)).append(" \n");
		sql.append("     GROUP BY ").append(" \n");
		sql.append("         period ").append(" \n");
		sql.append(" ) t ").append(" \n");
	}*/

}
